package com.sj.purchase.modules.cost.repository.price;

import com.sj.purchase.modules.cost.domain.price.CostPrice;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Page;
import org.springframework.data.jpa.repository.JpaRepository;

import java.util.Date;

/**
 * Created by yangrd on 2017/10/26
 **/
public interface CostPriceRepository extends JpaRepository<CostPrice, Long>, CostPriceRepositoryCustom {

    /**
     * 查詢該sku在date之前時間最近的一個
     *
     * @param skuNo
     * @param date
     * @return
     */
    CostPrice findFirstBySkuNoAndCreateDateBeforeOrderByCreateDateDesc(String skuNo, Date date);

    Page<CostPrice> findByCreateDateBetweenOrderByCreateDateDesc(Date startDate, Date endDate,Pageable pageable);

    Page<CostPrice> findBySkuNoOrderByCreateDateDesc(String skuNo, Pageable pageable);

    Page<CostPrice> findBySkuNoAndCreateDateBetweenOrderByCreateDateDesc(String skuNo,Date startDate, Date endDate,Pageable pageable);

/*    @Query(value = "select  a.sku_no,a.create_date, " +
            "LEFT(a.price_c,IF(LOCATE(',',a.price_c)=0,LENGTH(a.price_c),LOCATE(',',a.price_c)-1)) avg_cost_price, " +
            "LEFT(a.qty_c,IF(LOCATE(',',a.qty_c)=0,LENGTH(a.qty_c),LOCATE(',',a.qty_c)-1)) inventory_qty " +
            "FROM " +
            "(SELECT " +
            " t_cost_price.sku_no, " +
            " GROUP_CONCAT(t_cost_price.avg_cost_price ORDER BY t_cost_price.create_date desc) price_c,  " +
            " max(t_cost_price.create_date) create_date, " +
            " GROUP_CONCAT(t_cost_price.inventory_qty ORDER BY t_cost_price.create_date desc) qty_c " +
            "FROM " +
            "t_cost_price " +
            "GROUP BY t_cost_price.sku_no) a",
            nativeQuery = true)
    List<CostPricePO> findCostPricePOByMaxDate();*/
}
